
IF OBJECT_ID('dp_listnotice') IS NOT NULL
		DROP PROC dbo.dp_listnotice
GO

CREATE PROC dbo.dp_listnotice
AS
BEGIN
		SET NOCOUNT ON
/**
		2019-10-15 00:29:49 F 定时调用本过程, 用于找出有哪些客户的哪些问题需要发布预警信息

		包括:
		1.通过正在查询的语句,取得卡死的线程和时间(执行时间超过预设的)
		2.报表卡死(执行时间超过预设的)

		输出的数据集:
		rCust_ID
		xType预警类型, 1正在查询, 2报表卡死
		xInfo信息, 如果是报表卡死就是报表信息, 如果是正在查询的语句就是语句信息(语句或者过程名)
		xDateTime时间戳
		xInt 如果是正在查询的语句, 这是线程


		2019-11-02 00:34:08 V3


		-- 2019-11-14 23:35:17 V4
		修改输出数据集:
		uid,
		msg
*/
		-- 超时时间
		DECLARE @fTimeout INT, @fReportTimeout INT	-- 报表超时时间,秒


		

		SELECT @fTimeout = xInt FROM dbo.Dt_systemdata (NOLOCK) WHERE sID = 1002
		SELECT @fReportTimeout = xInt FROM dbo.Dt_systemdata (NOLOCK) WHERE sID = 1004
		SET @fTimeout = 1
		SET @fReportTimeout = 1





		-- 即时起, dt_querying_temp和dt_reportting只用作临时数据, SELECT后即清理或定期清理
		DECLARE @tMsg TABLE(rCust_ID INT, xMsg NVARCHAR(MAX))
		-- [预警]客户 A 数据库出现执行时间超长线程,目前耗时 35秒 .相关语句: ASFS
		INSERT INTO @tMsg (rCust_ID, xMsg)
		SELECT	 m.rCust_ID, 
				N'[预警]客户 ' + c.xName + N' (数据库)出现执行时间超长线程,目前耗时 ' + CAST(m.xCost / 1000 AS NVARCHAR(30)) + N'秒 .相关语句: ' + (CASE WHEN ISNULL(m.xParentQuery, '') <> '' THEN (CASE WHEN LEN(m.xParentQuery) > 200 THEN LEFT(m.xParentQuery, 200) ELSE m.xParentQuery END )ELSE '' END)  AS xMsg
		FROM dbo.dt_querying_temp AS m (NOLOCK)
		INNER JOIN dbo.ct_customer AS c (NOLOCK) ON c.sID = m.rCust_ID
		WHERE m.xCost > @fTimeout AND ISNULL(m.xTag, 0) <= 0

		UPDATE dbo.dt_querying_temp SET xTag = 1 WHERE xCost > @fTimeout AND ISNULL(xTag, 0) <= 0

		-- [预警]客户 A 数据库出现报表卡死/执行时间超长情况,名称:过程:参数:
		-- [预警]客户 A 数据库出现报表执行时间超长情况,名称:过程:参数:
		
		INSERT INTO @tMsg (rCust_ID, xMsg)
		SELECT	m.rCust_ID,
				N'[预警]客户 ' + ISNULL(c.xName, '') + N' 数据库出现报表卡死情况,名称:' + ISNULL(m.xFormCaption, '') + N',过程:' + ISNULL(m.xQueryName, '') + N',参数:' + ISNULL(m.xQueryParamList, '') AS xMsg
		FROM dbo.dt_reportjam_temp AS m
		INNER JOIN dbo.ct_customer AS c (NOLOCK) ON c.sID = m.rCust_ID
		WHERE m.xDuration < 0 AND ISNULL(m.xTag, 0) <= 0

		UPDATE dbo.dt_reportjam_temp SET xTag = 1 WHERE xDuration < 0 AND ISNULL(xTag, 0) <= 0

		INSERT INTO @tMsg (rCust_ID, xMsg)
		SELECT	m.rCust_ID,
				N'[预警]客户 ' + ISNULL(c.xName, '') + N' 数据库出现报表执行时间超长情况,名称:' + ISNULL(m.xFormCaption, '') + N',过程:' + ISNULL(m.xQueryName, '') + N',耗时:' + CAST(m.xDuration AS VARCHAR(30)) + N',参数:' + ISNULL(m.xQueryParamList, '') AS xMsg
		FROM dbo.dt_reportjam_temp AS m
		INNER JOIN dbo.ct_customer AS c (NOLOCK) ON c.sID = m.rCust_ID
		WHERE m.xDuration > @fReportTimeout AND ISNULL(m.xTag, 0) <= 0

		UPDATE dbo.dt_reportjam_temp SET xTag = 1 WHERE xDuration > @fReportTimeout AND ISNULL(xTag, 0) <= 0

		/**
		SELECT --m.rCust_ID,
               m.xMsg
		FROM @tMsg AS m
		**/
		-- INNER JOIN dbo.ct_customer AS c (NOLOCK)
		
		SELECT c.xWXOpenID, m.xMsg FROM @tMsg AS m INNER JOIN dbo.ct_customer_contact AS n (NOLOCK) ON n.mID = m.rCust_ID INNER JOIN dbo.dt_contact AS c (NOLOCK) ON c.sID = n.rCont_ID
		UNION
		SELECT c.xWXOpenID, m.xMsg FROM @tMsg AS m INNER JOIN dbo.dt_contact AS c (NOLOCK) ON 1 = 1  WHERE c.xIsAdmin = 1
        
		/**
		INNER JOIN (
				
				UNION 
				SELECT m.sID AS rCont_ID FROM dbo.dt_contact AS m (NOLOCK) WHERE m.xIsAdmin = 1
		) AS con on
		**/
		
		/**
		DECLARE @tResult TABLE(sID INT, rCust_ID INT, xType INT, xInfo NVARCHAR(MAX), xDateTime DATETIME, xInt INT)
		DECLARE @tResult2 TABLE(sID INT, rCust_ID INT, xType INT, xInfo NVARCHAR(MAX), xDateTime DATETIME, xInt INT)

		
		INSERT INTO @tResult (sID, rCust_ID, xType, xInfo, xDateTime, xInt)
		SELECT	m.sID, m.rCust_ID, 1, ISNULL(m.xProcName, IIF(LEN(m.xParentQuery) > 100, LEFT(m.xParentQuery, 100), m.xParentQuery)), DATEADD(SECOND, m.xStartTime / 1000, '1970-1-1 08:00:00'),
				m.sPID
		FROM dbo.dt_querying_temp AS m (NOLOCK)
		WHERE ISNULL(m.xTag, 0) <= 0
		-- DELETE dbo.dt_querying_temp FROM dbo.dt_querying_temp AS m  WHERE EXISTS(SELECT 1 FROM @tResult AS n WHERE n.mID = m.mID AND n.dID = m.dID)
		UPDATE m SET m.xTag = 1 FROM dbo.dt_querying_temp AS m INNER JOIN @tResult AS n ON m.sID = n.sID


		INSERT INTO @tResult2 (sID, rCust_ID, xType, xInfo, xDateTime, xInt)
		SELECT m.sID, m.rCust_ID, 1, m.xQueryName, DATEADD(SECOND, m.xQueryTime / 1000, '1970-1-1 08:00:00'), 0
		FROM dbo.dt_reportjam_temp AS m (NOLOCK)
		WHERE ISNULL(m.xTag, 0) <= 0

		UPDATE m SET xTag = 1 FROM dbo.dt_reportjam AS m INNER JOIN @tResult2 AS n ON n.sID = m.sID

		SELECT rCust_ID,
               xType,
               xInfo,
               xDateTime,
               xInt
		FROM @tResult
		UNION ALL
        SELECT rCust_ID,
               xType,
               xInfo,
               xDateTime,
               xInt
		FROM @tResult2
		*/
END
